-- Drop the session if it exists.  
IF EXISTS (SELECT * FROM sys.server_event_sessions WHERE name = 'MonitorWaits') 
    DROP EVENT SESSION MonitorWaits ON SERVER 
GO 
 /*
SELECT map_key, map_value , name
FROM sys.dm_xe_map_values
WHERE name = 'wait_types'
AND map_value LIKE '%RES%'
ORDER by 1
and map_value = 'CXPACKET'

*/
CREATE EVENT SESSION MonitorWaits ON SERVER 
ADD EVENT sqlos.wait_info 
    (ACTION(sqlserver.database_id,sqlserver.session_id/*,sqlserver.sql_text*/,sqlserver.plan_handle, package0.event_sequence,sqlos.cpu_id)
     WHERE opcode = 1 AND duration > 10 
     --AND wait_type = 111
      AND ((wait_type > 0 AND wait_type < 22) -- LCK_ waits
                    OR (wait_type > 31 AND wait_type < 38) -- LATCH_ waits
                    OR (wait_type > 47 AND wait_type < 54) -- PAGELATCH_ waits
                    OR (wait_type > 63 AND wait_type < 70) -- PAGEIOLATCH_ waits
                    OR (wait_type > 96 AND wait_type < 100) -- IO (Disk/Network) waits
                    OR (wait_type = 111/*107*/) -- RESOURCE_SEMAPHORE waits
                    OR (wait_type = 113) -- SOS_WORKER waits
                    OR (wait_type = 120) -- SOS_SCHEDULER_YIELD waits
                    OR (wait_type = 178) -- WRITELOG waits
                    OR (wait_type > 174 AND wait_type < 177) -- FCB_REPLICA_ waits
                    OR (wait_type = 186) -- CMEMTHREAD waits
                    OR (wait_type = 191/*187*/) -- CXPACKET waits
                    OR (wait_type = 207) -- TRACEWRITE waits
                    OR (wait_type = 269) -- RESOURCE_SEMAPHORE_MUTEX waits
                    OR (wait_type = 283) -- RESOURCE_SEMAPHORE_QUERY_COMPILE waits
                    OR (wait_type = 284) -- RESOURCE_SEMAPHORE_SMALL_QUERY waits
                    OR (wait_type = 514) -- PREEMPTIVE_OS_WRITEFILEGATHER waits
                )     
        --AND sqlserver.session_id > 50
        AND sqlserver.session_id = 52  /* session_id of connection to monitor */
      ) 
ADD TARGET package0.ring_buffer (SET max_events_limit=(10000))
WITH 
( 
    MAX_MEMORY = 8192KB,  
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,  
    MAX_DISPATCH_LATENCY = 1 SECONDS,
    --MEMORY_PARTITION_MODE = NONE,  
    TRACK_CAUSALITY = ON
    --STARTUP_STATE = OFF
) 
GO 
-- Start the session 
ALTER EVENT SESSION MonitorWaits ON SERVER STATE = START; 
GO 
  
  SELECT name, description,* FROM sys.dm_xe_objects WHERE name LIKE '%atatch%'

  SELECT name, description,* FROM sys.dm_xe_objects WHERE name LIKE 'wait_info%'
  select * from sys.dm_xe_objects where name like '%wait%'
  select * from sys.dm_xe_objects where object_type = 'target'

-- Go do the query 
  
-- Stop the event session 
ALTER EVENT SESSION MonitorWaits ON SERVER STATE = STOP; 
GO 
  
SELECT CAST(xest.target_data AS XML) xml_data--, xes.name, *
FROM sys.dm_xe_session_targets xest 
  JOIN sys.dm_xe_sessions xes ON xes.address = xest.event_session_address 
  JOIN sys.server_event_sessions ses ON xes.name = ses.name
WHERE xest.target_name = 'ring_buffer' AND xes.name = 'MonitorWaits'
  

DROP TABLE #T
SELECT 
    COALESCE(event_data.value('(event/data[@name="database_id"]/value)[1]', 'int'), 
             event_data.value('(event/action[@name="database_id"]/value)[1]', 'int')) AS database_id,
    event_data.value('(event/action[@name="session_id"]/value)[1]', 'int') AS [session_id],
    event_data.value('(event/data[@name="wait_type"]/text)[1]', 'nvarchar(4000)') AS [wait_type],
    event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint') AS [duration]
INTO #T
FROM 
(   SELECT XEvent.query('.') AS event_data 
    FROM 
    (    -- Cast the target_data to XML 
        SELECT CAST(target_data AS XML) AS TargetData 
        FROM sys.dm_xe_session_targets st 
        JOIN sys.dm_xe_sessions s 
            ON s.address = st.event_session_address 
        WHERE name = 'MonitorWaits' 
          AND target_name = 'ring_buffer'
    ) AS Data 
    -- Split out the Event Nodes 
    CROSS APPLY TargetData.nodes ('RingBufferTarget/event') AS XEventData (XEvent)   
) AS tab (event_data)
OPTION(MAXDOP 1)

SELECT 
  [session_id],
  [wait_type],
  SUM([duration]) AS total_duration_sec,
  MAX([duration])/1000. AS max_duration_sec,
  COUNT(*)
FROM #T
GROUP BY [session_id],[wait_type]
ORDER BY [session_id]


SELECT 
  activity_id,
  plan_handle,
  [session_id],
  cpu_id,
  [wait_type],
  --MIN([timestamp]) as started_wait,
  --MAX([timestamp]) as finished_wait,
  SUM([duration]) AS total_duration,
  MAX([duration]) AS max_duration,
  COUNT(*)
FROM
(
-- Extract the Event information from the Event Session 
SELECT 
    --event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,
    DATEADD(hh, 
        DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), 
        event_data.value('(event/@timestamp)[1]', 'datetime2')) AS [timestamp],

    COALESCE(event_data.value('(event/data[@name="database_id"]/value)[1]', 'int'), 
             event_data.value('(event/action[@name="database_id"]/value)[1]', 'int')) AS database_id,
    event_data.value('(event/action[@name="session_id"]/value)[1]', 'int') AS [session_id],
    event_data.value('(event/data[@name="wait_type"]/text)[1]', 'nvarchar(4000)') AS [wait_type],
--    event_data.value('(event/data[@name="opcode"]/text)[1]', 'nvarchar(4000)') AS [opcode],
    event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint') AS [duration],
--    event_data.value('(event/data[@name="max_duration"]/value)[1]', 'bigint') AS [max_duration],
--    event_data.value('(event/data[@name="total_duration"]/value)[1]', 'bigint') AS [total_duration],
    --event_data.value('(event/data[@name="signal_duration"]/value)[1]', 'bigint') AS [signal_duration],
    --event_data.value('(event/data[@name="completed_count"]/value)[1]', 'bigint') AS [completed_count],
    event_data.value('(event/action[@name="plan_handle"]/value)[1]', 'nvarchar(4000)') AS [plan_handle],
    event_data.value('(event/action[@name="attach_activity_id"]/value)[1]', 'varchar(50)') as attach_activity_id,
    event_data.value('(event/action[@name="cpu_id"]/value)[1]', 'int') as cpu_id,
    event_data.value('(event/action[@name="event_sequence"]/value)[1]', 'bigint') as event_sequence2,
    CAST(SUBSTRING(event_data.value('(event/action[@name="attach_activity_id"]/value)[1]', 'varchar(50)'), 1, 36) AS uniqueidentifier) AS activity_id,
    CAST(SUBSTRING(event_data.value('(event/action[@name="attach_activity_id"]/value)[1]', 'varchar(50)'), 38, 10) AS int) AS event_sequence
    --event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(4000)') AS [sql_text]
FROM 
(   SELECT XEvent.query('.') AS event_data 
    FROM 
    (    -- Cast the target_data to XML 
        SELECT CAST(target_data AS XML) AS TargetData 
        FROM sys.dm_xe_session_targets st 
        JOIN sys.dm_xe_sessions s 
            ON s.address = st.event_session_address 
        WHERE name = 'MonitorWaits' 
          AND target_name = 'ring_buffer'
    ) AS Data 
    -- Split out the Event Nodes 
    CROSS APPLY TargetData.nodes ('RingBufferTarget/event') AS XEventData (XEvent)   
) AS tab (event_data)
) T
GROUP BY 
activity_id,
plan_handle,[session_id],cpu_id,[wait_type]
ORDER BY activity_id
OPTION(MAXDOP 1)


  -- Cast the target_data to XML 
        SELECT CAST(target_data AS XML) AS TargetData , s.*
        FROM sys.dm_xe_session_targets st 
        JOIN sys.dm_xe_sessions s 
            ON s.address = st.event_session_address 
        WHERE name = 'MonitorWaits' 
          AND target_name = 'ring_buffer'


